package com.project.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SchemaHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "ereader.db";
	private static final int DATABASE_VERSION = 1;
	
	public SchemaHelper(Context context) {
		super(context,DATABASE_NAME,null,DATABASE_VERSION);
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sqlTableRss = "CREATE TABLE " + TableRss.TABLE_NAME
					+" (" +TableRss._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
					+TableRss._WEBSITE +" TEXT,"
					+TableRss._CATEGORY +" TEXT,"
					+TableRss._LINK +" TEXT,"
					+TableRss._PUBDATE +" TEXT,"
					+TableRss._DESCRIPTION +" TEXT,"
					+TableRss._CONTENT +" TEXT )";
		db.execSQL(sqlTableRss);
		
		String sqlTableArticle = "CREATE TABLE " + TableArticle.TABLE_NAME
				+" (" +TableArticle._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
				+TableArticle._GUID +" TEXT,"
				+TableArticle._WEBSITE +" TEXT,"
				+TableArticle._CATEGORY +" TEXT,"
				+TableArticle._TITLE +" TEXT,"
				+TableArticle._DESCRIPTION +" TEXT,"
				+TableArticle._PUBDATE +" TEXT,"
				+TableArticle._LINK +" TEXT,"
				+TableArticle._ENCLOSURE_URL +" TEXT,"
				+TableArticle._ENCLOSURE_TYPE +" TEXT,"
				+TableArticle._ENCLOSURE_LENGTH +" TEXT)";
		db.execSQL(sqlTableArticle);
	}
	
	@Override
	public void close() 
	{
	    super.close();    
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
				// KILL PREVIOUS TABLES IF UPGRADED
				db.execSQL("DROP TABLE IF EXISTS " + TableRss.TABLE_NAME);
				// CREATE NEW INSTANCE OF SCHEMA
				onCreate(db);
	}
	
	public long addTableRss(TableRss tableRss){
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		ContentValues cv = new ContentValues();
		cv.put(TableRss._WEBSITE, tableRss.getWebsite());
		cv.put(TableRss._CATEGORY, tableRss.getCategory());
		cv.put(TableRss._LINK, tableRss.getLink());
		cv.put(TableRss._PUBDATE, dateFormat.format(tableRss.getPubDate()));
		cv.put(TableRss._CONTENT, tableRss.getContent());
		cv.put(TableRss._DESCRIPTION, tableRss.getDescription());
		SQLiteDatabase sd = getWritableDatabase();
		long result = sd.insert(TableRss.TABLE_NAME, TableRss._CATEGORY, cv); 
		return result;
	}
	
	public void deleteTableRss(long id){
		String[] args = new String[]{String.valueOf(id)};
		SQLiteDatabase sd = getWritableDatabase();
		sd.delete(TableRss.TABLE_NAME, TableRss._ID+" = ?" , args );
	}
	
	public List<TableRss> listTableRssBy(String website)
	{
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[]{TableRss._ID,TableRss._WEBSITE,TableRss._LINK,TableRss._DESCRIPTION,TableRss._CATEGORY,TableRss._PUBDATE,TableRss._CONTENT,};
		String[] selectionArgs = new String[] {website };
		Cursor cursor = sd.query(TableRss.TABLE_NAME, cols, TableRss._WEBSITE+" = ?"
								, selectionArgs
								, null, null,TableRss._PUBDATE);
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		List<TableRss> tableRssCollection = new ArrayList<TableRss>();
		while(cursor.moveToNext()){
			TableRss item = new TableRss();
			String strPubDate = cursor.getString(cursor.getColumnIndex(TableRss._PUBDATE));
			Date pubDate = null;
			try {
				pubDate = dateFormat.parse(strPubDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			item.setId(cursor.getLong(cursor.getColumnIndex(TableRss._ID)));
			item.setWebsite(cursor.getString(cursor.getColumnIndex(TableRss._WEBSITE)));
			item.setCategory(cursor.getString(cursor.getColumnIndex(TableRss._CATEGORY)));
			item.setLink(cursor.getString(cursor.getColumnIndex(TableRss._LINK)));
			item.setPubDate(pubDate);
			item.setContent(cursor.getString(cursor.getColumnIndex(TableRss._CONTENT)));
			item.setDescription(cursor.getString(cursor.getColumnIndex(TableRss._DESCRIPTION)));
			tableRssCollection.add(item);
		}
		return tableRssCollection;
	}
	
	
	public long addTableArticle(TableArticle tableArticle){
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		ContentValues cv = new ContentValues();
		cv.put(TableArticle._GUID, tableArticle.getGuid());
		cv.put(TableArticle._WEBSITE, tableArticle.getWebsite());
		cv.put(TableArticle._CATEGORY, tableArticle.getCategory());
		cv.put(TableArticle._TITLE, tableArticle.getTitle());
		cv.put(TableArticle._DESCRIPTION, tableArticle.getDescription());
		cv.put(TableArticle._PUBDATE, dateFormat.format(tableArticle.getPubDate()));
		cv.put(TableArticle._LINK, tableArticle.getLink());
		cv.put(TableArticle._ENCLOSURE_URL, tableArticle.getEnclosureUrl());
		cv.put(TableArticle._ENCLOSURE_TYPE, tableArticle.getEnclosureType());
		cv.put(TableArticle._ENCLOSURE_LENGTH, tableArticle.getEnclosureLength());
		SQLiteDatabase sd = getWritableDatabase();
		long result = sd.insert(TableArticle.TABLE_NAME, TableArticle._TITLE, cv);
		return result;
	}
	
	public List<TableArticle> listTableArticleBy(String website,String category)
	{
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[]{TableArticle._ID
									,TableArticle._GUID
									,TableArticle._WEBSITE
									,TableArticle._CATEGORY
									,TableArticle._TITLE
									,TableArticle._DESCRIPTION
									,TableArticle._PUBDATE
									,TableArticle._LINK
									,TableArticle._ENCLOSURE_URL
									,TableArticle._ENCLOSURE_TYPE
									,TableArticle._ENCLOSURE_LENGTH
									};
		String[] selectionArgs = new String[] {website,category };
		Cursor cursor = sd.query(TableArticle.TABLE_NAME, cols, TableArticle._WEBSITE+" = ? and "+TableArticle._CATEGORY+" = ?"
								,selectionArgs
								, null, null,TableArticle._PUBDATE + " DESC ");
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		List<TableArticle> tableArticleCollection = new ArrayList<TableArticle>();
		while(cursor.moveToNext()){
			TableArticle item = new TableArticle();
			String strPubDate = cursor.getString(cursor.getColumnIndex(TableArticle._PUBDATE));
			Date pubDate = null;
			try {
				pubDate = dateFormat.parse(strPubDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			item.setId(cursor.getLong(cursor.getColumnIndex(TableArticle._ID)));
			item.setGuid(cursor.getString(cursor.getColumnIndex(TableArticle._GUID)));
			item.setWebsite(cursor.getString(cursor.getColumnIndex(TableArticle._WEBSITE)));
			item.setCategory(cursor.getString(cursor.getColumnIndex(TableArticle._CATEGORY)));
			item.setTitle(cursor.getString(cursor.getColumnIndex(TableArticle._TITLE)));
			item.setDescription(cursor.getString(cursor.getColumnIndex(TableArticle._DESCRIPTION)));
			item.setLink(cursor.getString(cursor.getColumnIndex(TableArticle._LINK)));
			item.setEnclosureUrl(cursor.getString(cursor.getColumnIndex(TableArticle._ENCLOSURE_URL)));
			item.setEnclosureType(cursor.getString(cursor.getColumnIndex(TableArticle._ENCLOSURE_TYPE)));
			item.setEnclosureLength(cursor.getString(cursor.getColumnIndex(TableArticle._ENCLOSURE_LENGTH)));
			item.setPubDate(pubDate);
			tableArticleCollection.add(item);
		}
		return tableArticleCollection;
	}
	
	public List<String> listExistTableArticleGuid(String[] guids){
		SQLiteDatabase sd = getWritableDatabase();
		String sql = "SELECT "+TableArticle._GUID+" FROM "+ TableArticle.TABLE_NAME 
					+" WHERE "+TableArticle._GUID +" IN ("+makePlaceHolders(guids.length)
					+")";
		Cursor cursor = sd.rawQuery(sql, guids);
		List<String> exsitTitle = new ArrayList<String>();
		while(cursor.moveToNext()){
			exsitTitle.add(cursor.getString(0));
		}
		return exsitTitle;
	}
	
	private String makePlaceHolders(int len){
		String str = "";
		for (int i = 0; i < len; i++) {
			if(i!=len-1)
				str+="?,";
			else
				str+="?";
		}
		return str;
	}
	

}
